Release 10.1A: OpenEdge Data Management:
DataServer for ODBC

RUN STORED–PROCEDURE statement

Runs a non-Progress stored procedure or allows you to send SQL to an SQL-based data source using a DataServer.

Syntax 

RUN STORED-PROCEDURE procedure
  [ integer-field = PROC-HANDLE ]
  [ NO-ERROR ]
  [ ( parameter [ , parameter ] ... ) ]

procedure

The name of the stored procedure that you want to run or the Progress built-in procedure name, send–sql–statement, to send SQL to an SQL-based data source.

integer–field = PROC–HANDLE

Assigns a value to the specified integer field or variable (integer–field) that uniquely identifies the stored procedure returning results from the non-OpenEdge database or that uniquely identifies the SQL cursor used to retrieve results from an ODBC-compliant data source.

NO–ERROR

Specifies that any ERROR conditions that the RUN STORED–PROCEDURE statement produces are suppressed. Before you close a stored procedure, check the ERROR–STATUS handle for information on any errors that occurred. You receive an error when you attempt to close a stored procedure that did not start. The NO-ERROR option must appear before any run-time parameter list.

parameter

A run-time parameter to be passed to the stored procedure. A parameter has the following syntax:

Syntax 
[ INPUT | OUTPUT | INPUT-OUTPUT ]
  [ PARAM parameter-name = ] expression

An expression is a constant, field name, variable name, or expression. INPUT is the default. OUTPUT and INPUT–OUTPUT parameters must be record fields or program variables.

If you run send–sql–statement for an SQL-based data source, you must pass a single character expression parameter containing the SQL statement you want the data source to execute.

If you do not specify parameter–name (the name of a keyword parameter defined by the stored procedure), you must supply all of the parameters in correct order. If you do specify parameter–name, you must precede your assignment statement with the keyword PARAM. If you do not supply a required parameter, and no default is specified in the stored procedure, you receive a run-time error.

Examples

This procedure runs the stored procedure pcust and writes the results of the stored procedure into the Progress 4GL buffer, proc–text–buffer: 

DEFINE VAR hdl AS INTEGER.

RUN STORED-PROCEDURE pcust hdl = PROC-HANDLE
(10, OUTPUT 0, OUTPUT 0) NO-ERROR.
IF ERROR-STATUS:ERROR
THEN DO:
  MESSAGE "Stored Procedure failed to run".
END.
FOR EACH proc-text-buffer WHERE PROC-HANDLE = hdl:
  DISPLAY proc-text-buffer.
END.
CLOSE STORED-PROCEDURE pcust WHERE PROC-HANDLE = hdl.

This procedure uses the send–sql–statement option of the RUN STORED–PROCEDURE statement to send SQL to an ODBC-compliant data source. It writes the results of the stored procedure into the Progress 4GL buffer, proc–text–buffer: 

DEFINE VAR handle1 AS INTEGER.

RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
     ("SELECT name, cust_num FROM customer").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
   DISPLAY proc-text_buffer.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

This code example shows how to trap errors from the non-OpenEdge RDBMS within a procedure: 

DEFINE VAR h1 AS INTEGER.
DEFINE VAR j AS INTEGER.
RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE NO-ERROR
  ("select count (*) from pcust.customer where name between ’A’ and ’Z’ ").

IF ERROR STATUS:ERROR THEN DO:
  DO j = 1 TO ERROR-STATUS:NUM-MESSAGES:
    MESSAGE "error" ERROR-STATUS:GET-NUMBER(j)
        ERROR-STATUS:GET-MESSAGE(j).
  END.
END. 
FOR EACH proc-text-buffer. 
  DISPLAY proc-text-buffer. 
END.

CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = h1.

Note

The RUN STORED–PROCEDURE statement starts a transaction with the same scope as transactions started with the UPDATE statement.

See also

CLOSE STORED–PROCEDURE statement, PROC–HANDLE function, PROC–STATUS function

Copyright © 2005 Progress Software Corporation
 www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095